# -*- coding: utf-8 -*-
# @Time : 2019/1/10 16:41
# @Author : Administrator
# @File : interface_data.py
# @Project : untitled1
# @title :
import xlrd
class data():
    def login_test1(self):
        with open(r"../data/test.xls",encoding="utf-8") as f:
            p = xlrd.open_workbook(r"../data/test.xls")
            p1 = p.sheets()[0]
            y = p1.nrows
            list1 = []
            key1 = p1.cell(0, 0).value
            key2 = p1.cell(0, 1).value
            key3 = p1.cell(0, 2).value
            key4 = p1.cell(0, 3).value
            # print(key1)
            for i in range(1, y):
                userName = p1.cell(i, 0).value
                userPass = p1.cell(i, 1).value
                checkcode = p1.cell(i, 2).value
                expect = p1.cell(i, 3).value
                dict={}
                dict[key1] = userName
                dict[key2] = userPass
                dict[key3] = checkcode
                dict[key4] = expect
                list1.append(dict)
            return list1
    def login_test2(self,num):
        with open(r"C:\Users\Administrator\PycharmProjects\untitled1\boss_1\case\data\test1.xls",encoding="utf-8") as f:
            p = xlrd.open_workbook(r"C:\Users\Administrator\PycharmProjects\untitled1\boss_1\case\data\test1.xls")
            p1 = p.sheets()[num]
            y = p1.nrows
            list2 = []
            # print(p1.cell(0, 0).value)
            key1 = p1.cell(0, 0).value
            key2 = p1.cell(0, 1).value
            key3 = p1.cell(0, 2).value
            key4 = p1.cell(0, 3).value
            key5= p1.cell(0, 4).value
            key6 = p1.cell(0, 5).value


            # print(key1)
            for i in range(1, y):
                name = p1.cell(i, 0).value
                number = p1.cell(i, 1).value
                sex = p1.cell(i, 2).value
                phone = p1.cell(i, 3).value
                idcard = p1.cell(i, 4).value
                expect = p1.cell(i, 5).value

                dict={}
                dict[key1] = name
                dict[key2] = number
                dict[key3] = sex
                dict[key4] = phone
                dict[key5] = idcard
                dict[key6] = expect
                list2.append(dict)
            return list2
    def login_test3(self):
        with open(r"C:\Users\Administrator\PycharmProjects\untitled1\boss_1\case\data\test2.xls",encoding="utf-8") as f:
            p = xlrd.open_workbook(r"C:\Users\Administrator\PycharmProjects\untitled1\boss_1\case\data\test2.xls")
            p1 = p.sheets()[0]
            y = p1.nrows
            list3 = []
            # print(p1.cell(0, 0).value)
            key1 = p1.cell(0, 0).value
            key2 = p1.cell(0, 1).value
            key3 = p1.cell(0, 2).value
            key4 = p1.cell(0, 3).value
            key5= p1.cell(0, 4).value
            key6 = p1.cell(0, 5).value
            key7 = p1.cell(0, 6).value
            key8 = p1.cell(0, 7).value
            key9 = p1.cell(0, 8).value


            # print(key1)
            for i in range(1, y):
                student_id = p1.cell(i, 0).value
                start_time= p1.cell(i, 1).value
                end_time = p1.cell(i, 2).value
                leave_type = p1.cell(i, 3).value
                days = p1.cell(i, 4).value
                stuName = p1.cell(i, 5).value
                reason = p1.cell(i, 6).value
                comment = p1.cell(i, 7).value
                expect = p1.cell(i, 8).value

                dict={}
                dict[key1] = student_id
                dict[key2] = start_time
                dict[key3] = end_time
                dict[key4] = leave_type
                dict[key5] = days
                dict[key6] = stuName
                dict[key7] = reason
                dict[key8] = comment
                dict[key9] = expect
                list3.append(dict)
            return list3

    def login_test4(self):
        with open(r"C:\Users\Administrator\PycharmProjects\untitled1\boss_1\case\data\data.xlsx",encoding="utf-8") as f:
            p = xlrd.open_workbook(r"C:\Users\Administrator\PycharmProjects\untitled1\boss_1\case\data\data.xlsx")
            p1 = p.sheets()[0]
            y = p1.nrows
            list4 = []
            # print(p1.cell(0, 0).value)
            key1 = p1.cell(0, 0).value
            key2 = p1.cell(0, 1).value
            key3 = p1.cell(0, 2).value
            key4 = p1.cell(0, 3).value
            key5= p1.cell(0, 4).value
            key6 = p1.cell(0, 5).value
            key7 = p1.cell(0, 6).value
            key8 = p1.cell(0, 7).value

            # print(key1)
            for i in range(1, y):
                student_id = p1.cell(i, 0).value
                start_time= p1.cell(i, 1).value
                end_time = p1.cell(i, 2).value
                leave_type = p1.cell(i, 3).value
                days = p1.cell(i, 4).value
                stuName = p1.cell(i, 5).value
                reason = p1.cell(i, 6).value
                comment = p1.cell(i, 7).value


                dict={}
                dict[key1] = student_id
                dict[key2] = start_time
                dict[key3] = end_time
                dict[key4] = leave_type
                dict[key5] = days
                dict[key6] = stuName
                dict[key7] = reason
                dict[key8] = comment
                list4.append(dict)
            return list4


if __name__ == '__main__':
    t=data()
    # print(t.login_test2())
    print(t.login_test4())